﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Citi.RoutineBase;
using Citi.Helpers;
using System.Configuration;
using Citi.SecurityHelper;

namespace Citi.GenericApplications.SchedulerUi
{
    public class ShrinkSqlDatabase : IExecutableRoutine
    {
        private string DropProcedureQuery = @"IF EXISTS (SELECT 1 from [dbo].[sysobjects] where name='sp_Shrink' AND xtype='P') DROP procedure sp_Shrink";

        private string CreateProcedureQuery = @"CREATE proc sp_Shrink
	                            as
	
	                            DECLARE @logFile varchar(310), @databaseName varchar(300);
	                            SELECT @databaseName = name FROM sys.sysfiles WHERE groupid = 1
	                            SELECT @logFile = name FROM sys.sysfiles WHERE groupid = 0

	                            DBCC SHRINKFILE (@databaseName , 0, TRUNCATEONLY)
	                            DBCC SHRINKFILE (@logFile, 0, TRUNCATEONLY)";

        private static string DatabaseConnectionString { get { return ConfigurationManager.AppSettings["DatabaseConnectionString"]; } }

        public IEnumerable<RoutineParameter> Parameters
        {
            get
            {
                return new List<RoutineParameter> { 
                    new RoutineParameter{ Description = "SQL Data source", Name ="DataSource", Nullable = false, Required = true, Type = typeof(string)},
                    new RoutineParameter{ Description = "Initial Catalog", Name ="InitialCatalog", Nullable = false, Required = true, Type = typeof(string)},
                    new RoutineParameter{ Description = "SQL User", Name ="SqlUser", Nullable = false, Required = true, Type = typeof(string)},
                    new RoutineParameter{ Description = "Pwd file. Only filename. E.g. ExcelPassword.pwd", Name ="SqlPwdFile", Nullable = false, Required = true, Type = typeof(string)},
                };
            }
        }

        public void Rollback(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            var dataSource = parameters["DataSource"].ToString();
            var initialCatalog = parameters["InitialCatalog"].ToString();
            var user = parameters["SqlUser"].ToString();
            var sqlPassword = parameters["SqlPwdFile"].ToString();

            var sqlConnectionString = string.Format(DatabaseConnectionString, dataSource, initialCatalog, user, "{0}");

            currentAction.Description = "Dropping procedure";
            var query = DropProcedureQuery;
            currentAction.Description = query;
            SecureSqlConnection.ExecuteNonQuery(query, sqlConnectionString, sqlPassword);

            currentAction.Description = "Creating procedure";
            query = CreateProcedureQuery;
            currentAction.Description = query;
            SecureSqlConnection.ExecuteNonQuery(query, sqlConnectionString, sqlPassword);

            query = "EXEC sp_Shrink";
            currentAction.Description = query;
            SecureSqlConnection.ExecuteNonQuery(query, sqlConnectionString, sqlPassword);
        }

        public void Stop(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }
    }
}
